When working with MySQL databases, you may encounter the need to change a database name. However, MySQL no longer supports a direct renaming option. The RENAME DATABASE command, available in earlier versions, was removed due to the risk of data loss. Despite this, renaming a database in MySQL is still possible. This article explores the available methods to accomplish this task safely and effectively.
Contents
- How to rename databases in MySQL using mysqldump shell commands
- How to rename databases in MySQL using dbForge Studio for MySQL
- How to rename databases in MySQL Workbench
- How to rename databases in phpMyAdmin
- How to rename databases in Shell CLI
- Best methods to rename a MySQL database
- Conclusion
How to rename databases in MySQL using mysqldump shell commands
Renaming a database in MySQL involves creating a new empty database with the desired name and transferring the contents from the old database. Once the transfer is complete, the old database can be archived or dropped as needed.
This process can be accomplished using the mysqldump utility, which enables you to create a dump of the old database and import its contents into the new one.
First, we need to dump the old database. Run the below command within your shell prompt to create a physical backup that has all database data along with stored procedures and functions:
$ mysqldump -u username -p"password" -R oldDbname > oldDbname.sqlThe next step is to create a new database with the desired name:
$ mysqladmin -u username -p"password" create newDbnameFinally, we import the dump file into the new database:
$ mysql -u username -p"password" newDbname < oldDbname.sqlThe drawback of this method is that it may take considerable time, especially when dealing with large databases.
Another way out is using visual tools, such as dbForge Studio for MySQL.
How to rename databases in MySQL using dbForge Studio for MySQL
Graphical user interfaces (GUI tools) are highly favored alternatives to the command line due to their intuitive design and the ability to speed up task performance by switching to visual mode.
This article will focus on dbForge Studio for MySQL, an IDE built to manage database-related tasks for MySQL and MariaDB in every aspect of database development, management, and administration. This includes SQL coding, database comparison and synchronization, data migration, user management, source control, and more, all within a user-friendly visual interface.
To rename databases in dbForge Studio for MySQL, you can choose from three options:
- Backup and restore
- Schema and data comparison and synchronization
- Database copying
Now, let us delve into each of these methods in detail.
Backup and restore a database under a different name
This method involves creating a backup of the database and restoring it into a new database with the desired name. With dbForge Studio for MySQL, this process is simple and intuitive.
For example, if you want to rename the sakila test database to sakila_new, follow these steps:
Click Database > Tasks > Backup Database, or select the desired database from the Database Explorer pane on the left, then choose Tasks > Backup Database from the context menu.
The Database Backup Wizard will be launched. Specify the connection, the database to create a backup, the path to the folder for storing the output backup file, and the backup file name.
In the Backup content section, include both the structure and all objects into the backup file.
Specify the additional options if necessary and the errors handling behavior if needed. Click Backup. When the backup file is created, click Finish.
The next step is to create a new database with the desired name, sakila_new. You can do this using the standard CREATE DATABASE command or through a visual interface as follows:
Go to Database > New Database. In the visual editor, enter the name of the database, sakila_new. Click Apply Changes to create the database.
The sakila_new database is now present in the Database Explorer list. It is an empty database, and we want to migrate the schema and data from sakila into sakila_new. We can achieve that with the help of the backup file we created earlier for sakila.
Right-click the sakila_new database in the Database Explorer pane, select Tasks > Restore Database.
In the Database Restore Wizard, check the details, browse to the backup file created, and select it. Click Restore.
We have successfully restored the sakila backup in the sakila_new database, and we have two identical databases under different names.
The database under the old name can be dropped, as all data are saved in a database under the new name.
Compare and synchronize database schemas and data
dbForge Studio for MySQL provides powerful tools for comparing and synchronizing database schemas and table data. While this functionality is typically used to identify and analyze discrepancies between databases across development, staging, and production environments, it can also copy a database schema and data into a new empty database.
To begin, create an empty database with the desired name, such as sakila_test. Once it appears in the list of databases, navigate to Comparison > New Schema Comparison to start the process.
In the New Schema Comparison window, define the databases to be compared. Set the older database as Source and the newly-created database with the desired name as Target. Click Compare.
You can see all schema objects that exist only in the Source database since our new database is empty. Click green arrow to start the schema synchronization process that will copy the sakila schema into the sakila_test database.
To confirm the results, refresh the schema comparison results. The schemas of these two databases are completely identical.
After we have synchronized the schemas, we need to synchronize the data as well. Similarly to the previous steps, click Comparison > New Data Comparison and set Source and Target for comparison.
Click Compare to start the data comparison process. Review the results and click green arrow.
When the task is complete, we will have two identical databases with different names.
Thus, we can drop the old database.
Copy a database with dbForge Studio for MySQL
The easiest method of creating a new database with the identical schema and data under the new name with one click is offered by the Copy Database option is available in dbForge Studio for MySQL.
Click Database > Tasks > Copy Database
Specify the Source and Target servers and select the database to copy from the Source column.
By default, the names from the Source column are duplicated to the Target column, but these names are editable. Specify a new name for the database in Target, renaming the copy.
Check Include Data to copy the MySQL database along with its data and click green arrow to start copying.
When the process is complete, you can see the copy of the database under the new name in the list of databases.
As you see, the process of renaming databases with dbForge Studio for MySQL doesn’t involve any difficulties as it is accomplished via an intuitive interface of the GUI tool. You can choose from the three options according to your goals and project requirements.
Apart from that, we cannot but mention that MySQL Studio delivers extensive Refactoring functionality that will help you rename columns, tables, and views while preserving database integrity.
How to rename databases in MySQL Workbench
MySQL Workbench is the default IDE designed for MySQL specialists. It is free of charge and offers decent functionality to perform various standard database tasks in MySQL, though its functionality is significantly less robust than in dbForge Studio for MySQL.
Database renaming is not supported in MySQL Workbench. It is usually recommended to use the mysqldump utility separately.
The available option suggests renaming tables to alter the database name of a particular table. This operation will transfer the table with all data from the old database into the new one under the desired name. InnoDB, the default MySQL storage engine, allows this.
Let’s create a new empty database under the new desired name in MySQL Workbench. Click the + icon in the Schemas section of the Navigator or use the CREATE DATABASE command.
Name the empty database and click Apply. The database will appear in the list under Schemas.
Apply the following command to change the database name of the particular table:
RENAME TABLE oldDbname.table TO newDbname.table;For instance, we want to transfer the actor table from the sakila database into the sakila_new database. Execute the below command:
RENAME TABLE sakila.actor TO sakila_new.actor;Refresh the database, and you can see that the table with all data has been transferred to sakila_new.
You can do it with all other tables to move them into the new database. However, the query allows moving tables one at a time, which isn’t practical for large databases. Besides, the RENAME TABLE command doesn’t work for views and triggers.
How to rename databases in phpMyAdmin
Many MySQL specialists rely on phpMyAdmin for their daily tasks. As a web-based application, it is accessible from any location, and it offers a range of tools for routine database management. On the other hand, it lacks advanced features found in more sophisticated alternatives like dbForge Studio for MySQL.
phpMyAdmin allows renaming databases by creating a copy under a new name and automatically deleting the old database. Let’s see how it’s done.
Select the necessary database from the list on the left and proceed to the Operations tab. Then, in the Rename database to field, enter the new database name. Click Go.
You will be prompted to confirm creating a database under the new name and dropping the old database. Click OK.
After that, the database under the new name will appear in the list, while the previous database under the old name is dropped.
This method is simple; however, it suggests immediate deletion of the database under the old name, which may cause risks of data loss if some failure takes place during the process.
How to rename databases in Shell CLI
Using Shell Command Line Interface (CLI) tools for database management is a common approach to automating repetitive tasks like backups, restorations, and data migrations. Renaming MySQL databases is one such task, which involves creating a new database with the desired name and transferring data from the old database.
By combining shell commands with MySQL commands, you can create scripts to make these tasks more efficient and reduce the risk of human error. Below is an example of a shell script for renaming databases:
# Define variables
$MySQLHost = "host"
$MySQLPort = "port"
$MySQLUser = "username"
$MySQLPassword = "password"
$NewDatabase = "new_db_name"
# Function to execute a MySQL command
function Execute-MySQLCommand($Command) {
$CommandString = "mysql -h $MySQLHost -P $MySQLPort -u $MySQLUser -p$MySQLPassword -e `"$Command`""
Write-Host "Executing: $CommandString"
Invoke-Expression $CommandString
}
# Step 1: Drop and create the new database
Write-Host "Dropping and creating new database: $NewDatabase..."
Execute-MySQLCommand "DROP DATABASE IF EXISTS $NewDatabase;"
Execute-MySQLCommand "CREATE DATABASE $NewDatabase;"
# Step 2: Dump the old database
Write-Host "Dumping old database: $OldDatabase to $DumpFilePath..."
cmd.exe /c "mysqldump -h $MySQLHost -P $MySQLPort -u $MySQLUser -p$MySQLPassword --default-character-set=utf8 db_old_name > D:\db_old_name_dump.sql"
# Step 3: Import data into the new database
Write-Host "Importing data from dump file to new database: $NewDatabase..."
# Restore DB
cmd.exe /c "mysql -h $MySQLHost -P $MySQLPort -u $MySQLUser -p$MySQLPassword $NewDatabase < D:\db_old_name_dump.sql"
# Completion message
Write-Host "Database migration completed successfully!"Be sure to replace placeholders with the actual values for host, port, username, and password. Also, specify the names of the old database (for dumping) and the new database (with the desired name).
Best methods to rename a MySQL database
We have explored various methods of renaming databases in MySQL, both via the command-line tools and using such GUI tools like dbForge Studio, MySQL Workbench, and phpMyAdmin. The below table compiles all these methods together.
| Tool | Available Methods | Advantages | Disadvantages |
| mysqldump | Create a new empty database and dump and import data under a new name | Uses standard commands; has fast performance | Requires familiarity with mysqldump |
| dbForge Studio for MySQL | Backup and restore a database under a new name; perform schema and data synchronization into a new database; copy a database | Supports multiple methods; visual tools simplify the process | Is only available in paid editions |
| MySQL Workbench | Transfer tables with data from old database into the new database with the desired name | Uses standard SQL commands | Allows transferring tables only, no views or triggers |
| phpMyAdmin | Copy a database under a new name and drop the old database | Visual tools simplify the process | Bears the risk of data loss due to the automatic deletion of the old database |
Conclusion
Renaming databases in MySQL is a common task for database administrators, requiring reliable methods to execute effectively. Although MySQL no longer supports the old direct renaming command, it provides alternatives. With the addition of GUI tools, such as dbForge Studio for MySQL, users can complete the process more quickly and easily without manually typing commands.
dbForge Studio offers several renaming methods, adaptable to various scenarios, among the many other options for database management in MySQL and MariaDB. To explore the Studio’s capabilities, download a 30-day fully functional free trial, install it, and experience its full potential.
